<?php
require_once "DatabaseManager.php";
require_once "UsersManager.php";


class CoursesManager extends DatabaseManager {
	
		private function getCourses($keyWord, $filter, $take, $skip) {
		 // Convert the keyWord to lower case
         $keyWord = str_replace("+", " ",$keyWord);
		 $keyWord = strtolower($keyWord);
		 $filter = strtolower($filter);
		 	
		 if ($filter == Filter::COURSE_NAME) {
			 // Search by course name
			$queryString = "SELECT DISTINCT code, name FROM courses WHERE LOWER(name) LIKE '$keyWord%' OR LOWER(name) LIKE '% $keyWord%' ORDER BY courses.name LIMIT $take OFFSET $skip";
             $totalQueryString = "SELECT count(*) as total FROM courses WHERE LOWER(name) LIKE '$keyWord%' OR LOWER(name) LIKE '% $keyWord%'";
		 } 
		 else if ($filter == Filter::COURSE_CODE) {
			 // Search by course code
		 	$queryString = "SELECT DISTINCT code, name FROM courses WHERE LOWER(code) LIKE '$keyWord%' ORDER BY courses.name LIMIT $take OFFSET $skip";
            $totalQueryString = "SELECT count(*) as total FROM courses WHERE LOWER(code) LIKE '$keyWord%'";
		 }
		 else if ($filter == Filter::COURSE_TEACHER) {
			 // Search by teacher's name
			 $queryString = "SELECT DISTINCT code, name FROM courses INNER JOIN teachers_courses ON courses.code = teachers_courses.course_code INNER JOIN teachers ON teachers.id = teachers_courses.teacher_id  WHERE LOWER(teachers.first_name) LIKE '$keyWord%' OR LOWER(teachers.last_name) LIKE '$keyWord%' ORDER BY courses.name LIMIT $take OFFSET $skip";
             $totalQueryString = "SELECT count(*) as total FROM courses INNER JOIN teachers_courses ON courses.code = teachers_courses.course_code INNER JOIN teachers ON teachers.id = teachers_courses.teacher_id  WHERE LOWER(teachers.first_name) LIKE '$keyWord%' OR LOWER(teachers.last_name) LIKE '$keyWord %'";
		 }
		 else {
			// Invalid Filter
			$jsonResults[MESSAGE] = Strings::INVALID_FILTER_MESSAGE;
			$this->response($this->toJSON($jsonResults), StatusCodes::STATUS_CODE_BAD_REQUEST);
		 }
		 $queryResults = mysql_query($queryString, $this->connection);
		 $numberOfRows = mysql_num_rows($queryResults);
		 if ($numberOfRows > 0) {
			// We found results matching the keyWord
			 $jsonCourses = array();
			 while ($row = mysql_fetch_array($queryResults)) {
				// Process each row
				$jsonCourse = array();
				// Course code
				$jsonCourse[JSON_Tags::COURSE_CODE] = $row['code'];
				// Course name
				$jsonCourse[JSON_Tags::COURSE_NAME] = $row['name'];
				// Self URI
				$jsonCourse[JSON_Tags::SELF_URL] = PROTOCOL.$_SERVER['SERVER_NAME'].EntryPoints::API.EntryPoints::COURSES.'/'.$row['code'];
				// Add the "JSON" Course
				array_push($jsonCourses, $jsonCourse);
			 }
             // Get total number of items
             $queryResults = mysql_query($totalQueryString, $this->connection);
             $row = mysql_fetch_array($queryResults);
             
             $jsonResults = array();
             $jsonResults[JSON_Tags::TOTAL_ITEMS] = $row['total'];
             $jsonResults[JSON_Tags::ITEMS] = $jsonCourses;
             
			 // Send the response
			 $this->response($this->toJSON($jsonResults),StatusCodes::STATUS_CODE_SUCCESS);
		 }
		 else {
			// No results
			$jsonResults[MESSAGE] = Strings::NO_COURSES_FOUND_MESSAGE;
			$this->response($this->toJSON($jsonResults), StatusCodes::STATUS_CODE_NOT_FOUND); 
		 }
			
	}
	
	private function getCourseInformation($courseCode) {
		$courseCode = strtolower($courseCode);
		// Query String
		$queryString = "SELECT c.code as course_code, c.name as course_name, f.name as faculty_name, f.id as faculty_id, f.url as faculty_uri, c.url as course_url FROM courses as c INNER JOIN faculties as  f ON c.faculty_id = f.id WHERE LOWER(c.code) = '$courseCode'";
		$queryResults = mysql_query($queryString, $this->connection);
		$numberOfRows = mysql_num_rows($queryResults);
		if ($numberOfRows > 0) {
			// Results were found
			$row = mysql_fetch_array($queryResults);
			$jsonResults = array();
			// Course code
			$jsonResults[JSON_Tags::COURSE_CODE] = $row['course_code'];
			// Course name
			$jsonResults[JSON_Tags::COURSE_NAME] = $row['course_name'];
			// Course URI
			$jsonResults[JSON_Tags::COURSE_URL] = $row['course_url'];
            // Faculty
            $facultyJSON = array();
            // Faculty Id
            $facultyJSON[JSON_Tags::FACULTY_ID] = $row['faculty_id'];
			// Faculty name
			$facultyJSON[JSON_Tags::FACULTY_NAME] = $row['faculty_name'];
			// Faculty URI 
			$facultyJSON[JSON_Tags::FACULTY_URL] = $row['faculty_uri'];
            // Faculty
            $jsonResults[JSON_Tags::FACULTY] = $facultyJSON;
			// Self URI
			$jsonResults[JSON_Tags::SELF_URL] = PROTOCOL.$_SERVER['SERVER_NAME'].EntryPoints::API.EntryPoints::COURSES.'/'.$row['course_code'];
			// Available Rooms for Course's Faculty
			$jsonResults[JSON_Tags::COURSE_AVAILABLE_ROOMS] =  PROTOCOL.$_SERVER['SERVER_NAME'].EntryPoints::API.EntryPoints::ROOMS."faculty".'/'.$row['faculty_id'];
			// Bookings_URL
			$jsonResults[JSON_Tags::COURSE_BOOKINGS_URL] = PROTOCOL.$_SERVER['SERVER_NAME'].EntryPoints::API.EntryPoints::BOOKINGS.EntryPoints::COURSES.'/'.$row['course_code'];
			
			// Teachers
			$queryString = "SELECT t.id as teacher_id, t.first_name as teacher_firstname, t.last_name as teacher_lastname, t.position as teacher_position, t.email_address as teacher_email, t.phone_number as teacher_phone, t.web as teacher_url FROM courses as c INNER JOIN teachers_courses as tc ON c.code = tc.course_code INNER JOIN teachers as t ON t.id = tc.teacher_id WHERE LOWER(c.code) = '$courseCode'"; 
			$teachers = array();
			$queryResults = mysql_query($queryString, $this->connection);
			$numberOfRows = mysql_num_rows($queryResults);
			if($numberOfRows > 0) {
				// If any teachers
				while ($row = mysql_fetch_array($queryResults)) {
					$teacherJSON = array();
					// Teacher Id
					$teacherJSON[JSON_Tags::TEACHER_ID] = $row['teacher_id'];
					// Teacher First Name
					$teacherJSON[JSON_Tags::TEACHER_FIRST_NAME] = $row['teacher_firstname'];
					// Teacher Last Name
					$teacherJSON[JSON_Tags::TEACHER_LAST_NAME] = $row['teacher_lastname'];
					// Teacher Position
					$teacherJSON[JSON_Tags::TEACHER_POSITION] = $row['teacher_position'];
					// Teacher Email
					$teacherJSON[JSON_Tags::TEACHER_EMAIL] = $row['teacher_email'];
					// Teacher Phone
					$teacherJSON[JSON_Tags::TEACHER_PHONE] = $row['teacher_phone'];
					// Teacher WEB
					$teacherJSON[JSON_Tags::TEACHER_URL] = $row['teacher_url'];
					
					array_push($teachers, $teacherJSON);
				}
			}
			$jsonResults[JSON_Tags::TEACHERS] = $teachers;
			
			// Study Groups
			$queryString = "SELECT s.id as study_id, s.name as study_name FROM courses as c INNER JOIN courses_studygroups as cs ON c.code = cs.course_code INNER JOIN study_groups as s ON s.id = cs.studygroup_id WHERE LOWER(c.code) = '$courseCode'"; 
			$studyGroups = array();
			$queryResults = mysql_query($queryString, $this->connection);
			$numberOfRows = mysql_num_rows($queryResults);
			if($numberOfRows > 0) {
				// If any Study Groups
				while ($row = mysql_fetch_array($queryResults)) {
					$studyJSON = array();
					// StudyGroup Id
					$studyJSON[JSON_Tags::STUDY_GROUP_ID] = $row['study_id'];
					// StudyGroup Name
					$studyJSON[JSON_Tags::STUDY_GROUP_NAME] = $row['study_name'];
				
					array_push($studyGroups, $studyJSON);
				}
			}
			$jsonResults[JSON_Tags::STUDY_GROUPS] = $studyGroups;
			
			// Types of classes
			$queryString = "SELECT t.id as type_of_class_id, t.name as type_of_class FROM courses as c INNER JOIN courses_typesofclasses as tc ON c.code = tc.course_code INNER JOIN types_of_classes as t ON t.id = tc.type_of_class_id WHERE LOWER(c.code) = '$courseCode'";
			$typesOfClasses = array();
			$queryResults = mysql_query($queryString, $this->connection);
			$numberOfRows = mysql_num_rows($queryResults);
			if($numberOfRows > 0) {
				// If any Types Of Classes
				while ($row = mysql_fetch_array($queryResults)) {
					$typesJSON = array();
					// Type of class id
					$typesJSON[JSON_Tags::TYPE_OF_CLASS_ID] = $row['type_of_class_id'];
					// Type of class name
					$typesJSON[JSON_Tags::TYPE_OF_CLASS_NAME] = $row['type_of_class'];
					
					array_push($typesOfClasses, $typesJSON);
				}
				// Add also the default ones
				$typesJSON = array();
				$typesJSON[JSON_Tags::TYPE_OF_CLASS_ID] = TypeOfClass::EXAMEN;
				$typesJSON[JSON_Tags::TYPE_OF_CLASS_NAME] = "Examen";
				array_push($typesOfClasses, $typesJSON);
				$typesJSON = array();
				$typesJSON[JSON_Tags::TYPE_OF_CLASS_ID] = TypeOfClass::RECUPERARE;
				$typesJSON[JSON_Tags::TYPE_OF_CLASS_NAME] = "Recuperare";
				array_push($typesOfClasses, $typesJSON);
				$typesJSON = array();
				$typesJSON[JSON_Tags::TYPE_OF_CLASS_ID] = TypeOfClass::CONSULTATII;
				$typesJSON[JSON_Tags::TYPE_OF_CLASS_NAME] = "Consultatii";
				array_push($typesOfClasses, $typesJSON);
                $typesJSON[JSON_Tags::TYPE_OF_CLASS_ID] = TypeOfClass::OTHER;
				$typesJSON[JSON_Tags::TYPE_OF_CLASS_NAME] = "Altele";
				array_push($typesOfClasses, $typesJSON);
			}
			$jsonResults[JSON_Tags::TYPES_OF_CLASSES] = $typesOfClasses;
			
			 // Send the response
			 $this->response($this->toJSON($jsonResults),StatusCodes::STATUS_CODE_SUCCESS);				
		}
		else {
			// Code not found
			$jsonResults[MESSAGE] = Strings::COURSE_CODE_NOT_VALID;
			$this->response($this->toJSON($jsonResults), StatusCodes::STATUS_CODE_NOT_FOUND);	
		}
	}
	
	private function getTypesOfClassesForCourse($courseCode) {
		$courseCode = strtolower($courseCode);
		$queryString = "SELECT t.id as type_of_class_id, t.name as type_of_class FROM courses as c INNER JOIN courses_typesofclasses as tc ON c.code = tc.course_code INNER JOIN types_of_classes as t ON t.id = tc.type_of_class_id WHERE LOWER(c.code) = '$courseCode'";
		$typesOfClasses = array();
		$queryResults = mysql_query($queryString, $this->connection);
		$numberOfRows = mysql_num_rows($queryResults);
		if($numberOfRows > 0) {
				// If any Types Of Classes
				while ($row = mysql_fetch_array($queryResults)) {
					$typesJSON = array();
					// Type of class id
					$typesJSON[JSON_Tags::TYPE_OF_CLASS_ID] = $row['type_of_class_id'];
					// Type of class name
					$typesJSON[JSON_Tags::TYPE_OF_CLASS_NAME] = $row['type_of_class'];
					
					array_push($typesOfClasses, $typesJSON);
				}
				// Add also the default ones
				$typesJSON = array();
				$typesJSON[JSON_Tags::TYPE_OF_CLASS_ID] = TypeOfClass::EXAMEN;
				$typesJSON[JSON_Tags::TYPE_OF_CLASS_NAME] = "Examen";
				array_push($typesOfClasses, $typesJSON);
				$typesJSON = array();
				$typesJSON[JSON_Tags::TYPE_OF_CLASS_ID] = TypeOfClass::RECUPERARE;
				$typesJSON[JSON_Tags::TYPE_OF_CLASS_NAME] = "Recuperare";
				array_push($typesOfClasses, $typesJSON);
				$typesJSON = array();
				$typesJSON[JSON_Tags::TYPE_OF_CLASS_ID] = TypeOfClass::CONSULTATII;
				$typesJSON[JSON_Tags::TYPE_OF_CLASS_NAME] = "Consultatii";
				array_push($typesOfClasses, $typesJSON);
				
		 // Send the response
		$this->response($this->toJSON($typesOfClasses),StatusCodes::STATUS_CODE_SUCCESS);			
		} else {
			// Code not found
			$jsonResults[MESSAGE] = Strings::COURSE_CODE_NOT_VALID;
			$this->response($this->toJSON($jsonResults), StatusCodes::STATUS_CODE_NOT_FOUND);
		}
	}
    
    private function getStudyGroupsForCourse($courseCode) {
        $courseCode = strtolower($courseCode);
        $queryString = "SELECT path FROM courses_studygroups AS cs INNER JOIN study_groups AS s ON cs.studygroup_id = s.id WHERE cs.course_code = '$courseCode'";
        $studyGroups = array();
		$queryResults = mysql_query($queryString, $this->connection);
		$numberOfRows = mysql_num_rows($queryResults);
		if($numberOfRows > 0) {
                //Results where found
                while ($row = mysql_fetch_array($queryResults)) {
                    $path = $row['path'];
                    $queryString = "SELECT * FROM study_groups WHERE path LIKE '$path%' ORDER BY path";
                    $studyGroupsQueryResults = mysql_query($queryString, $this->connection);
                    while ($studyGroupRow = mysql_fetch_array($studyGroupsQueryResults)) {
                        $studyGroup = array();
                        $studyGroup[JSON_Tags::STUDY_GROUP_ID] = $studyGroupRow['id'];
                        $studyGroup[JSON_Tags::STUDY_GROUP_NAME] = $studyGroupRow['name'];
                        array_push($studyGroups, $studyGroup);
                    }
                }
                // Send the response
                $this->response($this->toJSON($studyGroups),StatusCodes::STATUS_CODE_SUCCESS);
        } else {
            // Code not found
			$jsonResults[MESSAGE] = Strings::NO_STUDY_GROUPS_FOUND;
			$this->response($this->toJSON($jsonResults), StatusCodes::STATUS_CODE_NOT_FOUND);
        }
    }
		
	public function processApi() {
		// Check authorization token
		$userManager = new UsersManager();
		$userInfo = $userManager->getUserInfoForAuthorization();
		
		$METHOD = $this->get_request_method();
		if ($METHOD == "GET") {
			if (isset($_GET['keyword']) && isset($_GET['take']) && isset($_GET['skip']) && isset($_GET['filter'])) {
				// Get course with filter
				$keyword = addslashes($_GET['keyword']);
				$filter = addslashes($_GET['filter']);
				$take = $_GET['take'];
				$skip = $_GET['skip'];
				if (!is_numeric($take)) {
					// Take should be numeric
					$jsonResults[MESSAGE] = Strings::INVALID_TAKE;
					$this->response($this->toJSON($jsonResults), StatusCodes::STATUS_CODE_NOT_FOUND); 
				}
				if (!is_numeric($skip)) {
					// Skip should be numeric
					$jsonResults[MESSAGE] = Strings::INVALID_SKIP;
					$this->response($this->toJSON($jsonResults), StatusCodes::STATUS_CODE_NOT_FOUND); 
				}
				$this->getCourses($keyword, $filter, $take, $skip);
			}
		  else if (isset($_GET['courseCode']) && isset($_GET['action'])) {
 				//Get type of classes for a course
				if ($_GET['action'] == 'typesofclasses') {
					$courseCode = addslashes($_GET['courseCode']);
					$this->getTypesOfClassesForCourse($courseCode);
				} else if ($_GET['action'] == 'studygroups') {
                    $courseCode = addslashes($_GET['courseCode']);
					$this->getStudyGroupsForCourse($courseCode);
                } else {
					// Bad request
					$jsonResults[MESSAGE] = Strings::BAD_REQUEST;
					$this->response($this->toJSON($jsonResults), StatusCodes::STATUS_CODE_BAD_REQUEST);	
				}
		  } else if (isset($_GET['courseCode'])) {
				// Get course information
				$courseCode = addslashes($_GET['courseCode']);
				$this->getCourseInformation($courseCode); 			  
		  } else {
			  // Bad request
			$jsonResults[MESSAGE] = Strings::BAD_REQUEST;
			$this->response($this->toJSON($jsonResults), StatusCodes::STATUS_CODE_BAD_REQUEST);	
		  }
		} else {
			// Not supported method	
			$jsonResults[MESSAGE] = Strings::HTTP_METHOD_NOT_SUPPORTED;
			$this->response($this->toJSON($jsonResults), StatusCodes::STATUS_CODE_BAD_REQUEST);
		}
	}
}
?>